﻿using System.Data;
using ContractManagementSystem.EntityObjects;

namespace ContractManagementSystem.DataAccessLayer
{
    public static class ConsultantDao
    {

        public static DataTable SelectAllData()
        {
            var dataTable = CommonDao.GetDataTable("SP_Consultant_GetConsultant");
            return dataTable;
        }

        public static DataTable SelectConsultantById(string consultantId)
        {
            var query = "SP_Consultant_GetConsultantById @ConsultantId='" + consultantId + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        public static DataTable SelectConsultantByUserName(string user)
        {
            var query = "[sp_Consultant_GetConsultantByUserName] @UserName='" + user + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        /// <summary>
        /// Search consultant by fullname and company name
        /// </summary>
        /// <param name="fullName"></param>
        /// <param name="companyName"></param>
        /// <returns></returns>
        public static DataTable SearchConsultant(string fullName, string companyName)
        {
            var query = "sp_Consultant_GetConsultantByFullNameAndCompanyName @FullName='" + fullName + "',"
                        + "@CompanyName='" + companyName + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }

        #region"insert/delete/update"

        public static string InsertConsultant(ConsultantEntity consultant)
        {
            var sqlInsert = "SP_Consultant_InsertConsultant "
                            + "@UserName='" + consultant.UserName + "',"
                            + "@FullName=N'" + consultant.FullName
                            + "'," + "@Email='" + consultant.Email
                            + "'," + "@CompanyName=N'" + consultant.CompanyName
                            + "'," + "@PhoneNumber='" + consultant.PhoneNumber
                            + "'," + "@Address=N'" + consultant.Address + "'";
            return CommonDao.ExecuteSqlCommand(sqlInsert);
        }

        public static string UpdateConsultant(ConsultantEntity consultant)
        {
            var sqlUpdate = "SP_Consultant_UpdateConsultant "
                            + "@ConsultantId='" + consultant.ConsultantId + "',"
                            + "@FullName=N'" + consultant.FullName+ "'," 
                            + "@Email='" + consultant.Email+ "'," 
                            + "@CompanyName=N'" + consultant.CompanyName+ "',"
                            + "@PhoneNumber='" + consultant.PhoneNumber+ "'," 
                            + "@Address=N'" + consultant.Address + "'";
            return CommonDao.ExecuteSqlCommand(sqlUpdate);
        }

        public static string DeleteConsultant(ConsultantEntity consultant)
        {
            CommonDao.CreateConnection();
            var sqlDelete = "SP_Consultant_DeleteConsultant @ConsultantId ='" + consultant.ConsultantId + "'";

            return CommonDao.ExecuteSqlCommand(sqlDelete);
        }
        #endregion

        public static bool CheckExistEmailValue(string value)
        {
            string sql = "SELECT Email FROM tblConsultants WHERE Email = '" + value + "'";
            DataTable dataTable = CommonDao.GetDataTable(sql);
            if (dataTable.Rows.Count > 0)
                return true;
            return false;
        }
    }
}
